package com.cyt.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cyt.entity.SPFeiLei;
import com.cyt.entity.SheJiShi;
import com.cyt.entity.XingWenFengLei;
import com.cyt.util.DBManager;

public class SheJiShiDaoImpl {
	
	/**
	 * 查询设计师表所有
	 * @return
	 */
	public List<SheJiShi> getSJSsList() {
		List<SheJiShi> list = new ArrayList<SheJiShi>();
		Statement st = null;
		ResultSet rs = null;
		Connection conn = DBManager.getConnection();
		try {
			st = conn.createStatement();
			
			String sql = "SELECT * FROM shejishi";
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("id");
				String touxiang = rs.getString("touxiang");
				String name = rs.getString("name");
				String zhiwei = rs.getString("zhiwei");
				SheJiShi sjs = new SheJiShi(id, touxiang, name, zhiwei);// 创建用户对象
				list.add(sjs);
			}
			return list;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}

	}
	
	/**
	 * 查询设计师表所有 分页
	 * @return
	 */
	public List<SheJiShi> getSJS(int page, int size) {
		List<SheJiShi> list = new ArrayList<SheJiShi>();
		Statement st = null;
		ResultSet rs = null;
		Connection conn = DBManager.getConnection();
		try {
			st = conn.createStatement();
			
			String sql = "SELECT * FROM shejishi LIMIT "+(page-1)*size+","+size;
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("id");
				String touxiang = rs.getString("touxiang");
				String name = rs.getString("name");
				String zhiwei = rs.getString("zhiwei");
				SheJiShi sjs = new SheJiShi(id, touxiang, name, zhiwei);// 创建用户对象
				list.add(sjs);
			}
			return list;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}

	}
	
	/**
	 * 根据id删除设计师
	 * @param sheJiShiId要删除的设计师的id
	 * @return 返回数据库受影响行数
	 */
	public int shanChuSheJiShi(int sheJiShiId) {
		return DBManager.executeUpdate("DELETE FROM shejishi WHERE id="+sheJiShiId);
	}
	
	
	/**
	 * 通过id主键删除
     * 批量删除
	 * @param 
	 * @return
	 */
	public int shanSheJiShiS(String ids) {
		String sql = "delete from shejishi where id in("+ids+")";
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
	/**
	 * 添加设计师
	 * @param sjs
	 * @return
	 */
	public int addSheJiShi(SheJiShi sjs ) {
		return DBManager.executeUpdate("INSERT into shejishi VALUES(null,'"+sjs.getTouxiang()+"','"+sjs.getName()+"','"+sjs.getZhiwei()+"')");
	}
	
	/**
	 * 根据id设计师,以供修改的数据回显
	 * @param id
	 * @return
	 */
	public SheJiShi chaSheJiShiId(int sjsid) {
		SheJiShi sjs=null;
		Statement st = null;
		ResultSet rs = null;
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		// 2.创建Statement执行对象(用来执行sql语句)
		try {// try 正常执行
			st = conn.createStatement();
			String sql = "SELECT * FROM shejishi WHERE id="+sjsid;
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("id");
				String touxiang = rs.getString("touxiang");
				String name = rs.getString("name");
				String zhiwei = rs.getString("zhiwei");
				sjs = new SheJiShi(id, touxiang, name, zhiwei);
				
			}
			return sjs;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}

	}
	
	/**
	 * 修改设计师详细  根据id
	 * @param sjs
	 * @return
	 */
	public int xiuGaiSheJiShi(SheJiShi sjs ) {
		String sql="UPDATE shejishi set touxiang='"+sjs.getTouxiang()+"',name='"+sjs.getName()+"',zhiwei='"+sjs.getZhiwei()+"' WHERE id="+sjs.getId();

		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
}
